package com.ai.dmc.utils.sql;

import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Title： SQL语句解析器类
 * Description： SQL语句解析器类
 * Copyright：Copyright (c) 2009
 * Company：亚信科技（中国）有限公司
 * @author lg, 2015年10月23日
 * @version 1.0
 */
public class SqlParser{   
	public static final String SELECT = "select";
	public static final String UPDATE = "update";
	public static final String DELETE = "delete";
	public static final String INSERT = "insert";
	public static final String TRUNCATE = "truncate";
	public static final String CREATE = "create";
	public static final String DROP = "drop";
	
    /**
     * 解析选择的列
     */
   
    public static String parseCols(String sql){
        //SQL中选择的列
        String cols ="";
        sql = sql.trim();
        String regex="(select)(.+)(from)";   
        cols=getMatchedString(regex,sql);
        if(cols != null){
        	return cols.toLowerCase().trim();
        }
        return null;
    }
    
    /**
     * 解析选择的表
     */
    public static String parseTable(String sql){
        String regex="";   
         //SQL中查找的表
        String table ="";
        sql = sql.trim();
        if(isContains(sql,"\\s+where\\s+")){
            regex="(from)(.+)(where)";   
        }else{
            regex="(from)(.+)($)";   
        }
        table=getMatchedString(regex,sql);
        if(table != null){
        	return table.toLowerCase().trim();
        }
        return null;
    }
    
    
    
    /**
     * 从文本text中找到regex首次匹配的字符串，不区分大小写
     * @param regex： 正则表达式
     * @param text：欲查找的字符串
     * @return regex首次匹配的字符串，如未匹配返回空
     */
    private static String getMatchedString(String regex,String text){
        Pattern pattern=Pattern.compile(regex,Pattern.CASE_INSENSITIVE);
        
        Matcher matcher=pattern.matcher(text);
 
        while(matcher.find()){
            return matcher.group(2);
        }
        
        return null;
    }
    
    /**
     * 从文本text中找到regex首次匹配的字符串，不区分大小写
     * @param regex： 正则表达式
     * @param text：欲查找的字符串
     * @return regex首次匹配的字符串，如未匹配返回空
     */
    private static String getMatchedStringOrder(String regex,String text){
        Pattern pattern=Pattern.compile(regex,Pattern.CASE_INSENSITIVE);
        
        Matcher matcher=pattern.matcher(text);
 
        while(matcher.find()){
            return matcher.group(3);
        }
        
        return null;
    }
    
    /**
     * 看word是否在lineText中存在，支持正则表达式
     * @param lineText
     * @param word
     * @return
     */
    private static boolean isContains(String lineText,String word){
        Pattern pattern=Pattern.compile(word,Pattern.CASE_INSENSITIVE);
        Matcher matcher=pattern.matcher(lineText);
        return matcher.find();
    }
    
    public static boolean isSelectSql(String sql){
		boolean b = true;
		if(sql == null || sql.trim().equals("")){
			return false;
		}
		sql = sql.trim();
		sql = sql.toLowerCase();
		if(sql.contains(TRUNCATE) || sql.contains(DROP) || sql.contains(UPDATE) 
				|| sql.contains(DELETE) || sql.contains(INSERT)){
			//不符合要求的sql直接返回错误
			b =  false;
		}
		return b;
	}
    
    public static boolean contains(String sql,String regExp){
	    Pattern pattern=Pattern.compile(regExp,Pattern.CASE_INSENSITIVE);
	    Matcher matcher=pattern.matcher(sql);
	    return matcher.find();
	}

    
    /**
     * 测试
     * @param args
     */
    public static void main(String[] args){
        List<String> ls=new ArrayList<String>();
        ls.add("Select c1,c2,c3 From t1,t2,t3 Where condi1=5 and "
                + "condi6=6 or condi7=7 Group  by g1,g2,g3 order  by g2,g3");
        for(String sql:ls){
            System.out.println(parseCols(sql));
            System.out.println(parseTable(sql));
        }
    }

	public static String parseOrder(String sql) {
		//SQL中选择的列
        String cols ="";
        sql = sql.trim();
        String reg="(order)(.+)(by)(.+)";   
        cols = getMatchedStringOrder(reg,sql);
        if(cols != null && (cols.contains("desc") || cols.contains("DESC"))){
        	cols = cols.replace("desc", "").replace("DESC", "");
        }
        return cols.toLowerCase().trim();
	}
}
    
      

